/**
* Copyright (c) 2008,中企动力华南研发中心
* All rights reserved.
*/
package com.ce.pms.custservice.dao.impl;

import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

import com.ce.common.util.BlankUtil;
import com.ce.pms.base.dao.impl.PmsBaseDaoImpl;
import com.ce.pms.common.util.SqlConfig;
import com.ce.pms.custservice.dao.ICustServiceEventDao;
import com.ce.pms.custservice.model.CustServiceEventDO;
import com.ce.pms.custservice.vo.CustServiceEventSearchVO;
import com.ce.pms.custservice.vo.CustServiceEventVO;
import com.sitechasia.webx.core.support.Page;

/**
 * @author 高嵩 编写日期:2008-12-15
 * 客户事件DAOImpl
 */
public class CustServiceEventDaoImpl extends PmsBaseDaoImpl<CustServiceEventDO> implements ICustServiceEventDao{

	private static final String stringStr = "-";
	private SqlConfig sqlConfig;
	private final String QUERY_ASSOCIATECS_EVENT="queryAssociatedCSEventByPage";
	public CustServiceEventDaoImpl(){
		sqlConfig=new SqlConfig("CustEventSQL.xml");
	}

	/***
	 * 功能描述:  查询客服事件列表<br>
	 * @param custServiceEventSearchVO
	 * @return List<CustServiceEventVO>
	 * @roseuid 494329B00399
	 * Create author:高嵩 <br>
	 * Create on:2008-12-23  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public List<CustServiceEventDO> queryCustServEventList(CustServiceEventSearchVO custServiceEventSearchVO) {

		Map<String,Object> paramMap = new HashMap<String,Object>();
		StringBuffer strBuf = new StringBuffer("from CustServiceEventDO cse where 1=1 ");
		strBuf.append(ConstructionConditions(custServiceEventSearchVO,paramMap));
		strBuf.append(ConstructionSort(custServiceEventSearchVO));
		Query query = super.createNameQuery(strBuf.toString(), paramMap);
		Assert.notEmpty(query.list());
		return query.list();
	}

	/***
	 * 功能描述:  查询关联事件<br>
	 * @param custServiceEventSearchVO
	 * @return List<CustServiceEventVO>
	 * @roseuid 494345BF0128
	 * Create author:高嵩 <br>
	 * Create on:2008-12-23  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public List<CustServiceEventVO> queryAssociatedCSEventList(CustServiceEventSearchVO custServiceEventSearchVO) {

		String sql =sqlConfig.getSql(QUERY_ASSOCIATECS_EVENT);
		Query query = super.createSQLQuery(sql, new Object[]{custServiceEventSearchVO.getKeyword(),custServiceEventSearchVO.getKeyword()});
		Assert.notEmpty(query.list());
		List<Object[]> resultList =query.list();
		List<CustServiceEventVO> cusDOList = new ArrayList<CustServiceEventVO>();
		for(Object[] item : resultList){
			CustServiceEventVO custServiceEventVO=new CustServiceEventVO();
			custServiceEventVO.setKeyword(((BigInteger)item[0]).longValue());
			custServiceEventVO.setEventNo((String)item[1]);//事件编号
			custServiceEventVO.setFullAddress((String)item[2]);//详细地点
			custServiceEventVO.setHouseNumber((String)item[3]);//房号
			custServiceEventVO.setEmergencyDegreeName((String)item[4]);//紧急程度
			custServiceEventVO.setCustServEventNatureName((String)item[5]);//事件性质
			custServiceEventVO.setContact((String)item[6]);//联系人
			custServiceEventVO.setEventContent((String)item[7]);//事件内容
			custServiceEventVO.setValidityStr((String)item[8]);//有效性
			custServiceEventVO.setEventStatuzStr((String)item[9]);//事件状态
			cusDOList.add(custServiceEventVO);
		}
		return cusDOList;
	}


	/***
	 * 功能描述: 按页查询客服事件 <br>
	 * @param custServiceEventSearchVO
	 * @return
	 * Create author:高嵩 <br>
	 * Create on:2008-12-23  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public Page queryCustServEventByPage(CustServiceEventSearchVO custServiceEventSearchVO) {

		Map<String,Object> paramMap = new HashMap<String,Object>();
		StringBuffer strBuf = new StringBuffer("from CustServiceEventDO cse where 1=1 ");
		strBuf.append(ConstructionConditions(custServiceEventSearchVO,paramMap));
		strBuf.append(ConstructionSort(custServiceEventSearchVO));
		return super.pagedNameQueryByPageNo(strBuf.toString(),
				custServiceEventSearchVO.getPageNo(),
				custServiceEventSearchVO.getPageSize(), paramMap);

	}

	/***
	 * 功能描述: 构造查询条件 <br>
	 * @param custServiceEventSearchVO
	 * @param paramMap
	 * @return
	 * Create author:高嵩 <br>
	 * Create on:2008-12-23  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	private String ConstructionConditions(CustServiceEventSearchVO custServiceEventSearchVO,Map<String,Object> paramMap){

		StringBuffer strBuf = new StringBuffer();

		//事件编号
		if(StringUtils.hasText(custServiceEventSearchVO.getEventNo())){
			strBuf.append(" and cse.eventNo like ").append(":eventNo");
			paramMap.put("eventNo", "%"+custServiceEventSearchVO.getEventNo().trim()+"%");
		}
		//联系人
		if(StringUtils.hasText(custServiceEventSearchVO.getContact())){
			strBuf.append(" and cse.contact like ").append(":contact");
			paramMap.put("contact", "%"+custServiceEventSearchVO.getContact().trim()+"%");
		}
		//客户姓名--
		if(StringUtils.hasText(custServiceEventSearchVO.getOwners())){
			strBuf.append(" and cse.owners like ").append(":owners");
			paramMap.put("owners", "%"+custServiceEventSearchVO.getOwners().trim()+"%");
		}
		//房号houseNumber--
		if(StringUtils.hasText(custServiceEventSearchVO.getHouseNumber())){
			strBuf.append(" and cse.houseNumber like ").append(":houseNumber");
			paramMap.put("houseNumber", "%"+custServiceEventSearchVO.getHouseNumber().trim()+"%");
		}
		//详细地址fullAddress--
		if(StringUtils.hasText(custServiceEventSearchVO.getFullAddress())){
			strBuf.append(" and cse.fullAddress like ").append(":fullAddress");
			paramMap.put("fullAddress", "%"+custServiceEventSearchVO.getFullAddress().trim()+"%");
		}
		//事件区域
		if(null!=custServiceEventSearchVO.getEventRegionKeyword() && !"".equals(custServiceEventSearchVO.getEventRegionKeyword())){
			strBuf.append(" and cse.eventRegionDO.id = :eventRegionname");
			paramMap.put("eventRegionname", custServiceEventSearchVO.getEventRegionKeyword());
		}
		//事件性质
		if(null!=custServiceEventSearchVO.getEventNatureKeyword() && !"".equals(custServiceEventSearchVO.getEventNatureKeyword())){
			strBuf.append(" and cse.customerServiceEventNatureDO.id = :EventNatureID");
			paramMap.put("EventNatureID", custServiceEventSearchVO.getEventNatureKeyword());
		}

		//事件状态
		if(StringUtils.hasText(custServiceEventSearchVO.getEventStatuz())){
			strBuf.append(" and cse.eventStatuz = :eventStatuz");
			paramMap.put("eventStatuz", custServiceEventSearchVO.getEventStatuz().trim());
		}
		//回访状态
		if(StringUtils.hasText(custServiceEventSearchVO.getReturnVisitStatuz())){
			if(stringStr.equals(custServiceEventSearchVO.getReturnVisitStatuz())){
				strBuf.append(" and cse.returnVisitStatuz is NULL");

			}else{
				strBuf.append(" and cse.returnVisitStatuz = :returnVisitStatuz");
				paramMap.put("returnVisitStatuz", custServiceEventSearchVO.getReturnVisitStatuz().trim());
			}

		}
		//阅签状态reviewerStatuz
		if(StringUtils.hasText(custServiceEventSearchVO.getReviewerStatuz())){
			if(stringStr.equals(custServiceEventSearchVO.getReviewerStatuz())){
				strBuf.append(" and cse.reviewerStatuz is NULL");

			}else{
				strBuf.append(" and cse.reviewerStatuz = :reviewerStatuz");
				paramMap.put("reviewerStatuz", custServiceEventSearchVO.getReviewerStatuz().trim());
			}

		}
		//记录人
		if(StringUtils.hasText(custServiceEventSearchVO.getRecorder())){
			strBuf.append(" and cse.recorder = :recorder");
			paramMap.put("recorder", custServiceEventSearchVO.getRecorder().trim());
		}
//		记录时间(开始记录时间)
		if(null!=custServiceEventSearchVO.getRecorderStartTime()){
			System.out.println(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(custServiceEventSearchVO.getRecorderStartTime()));
			strBuf.append(" and cse.recorderTime >= :recorderStartTime");
			paramMap.put("recorderStartTime", custServiceEventSearchVO.getRecorderStartTime());
		}
//		记录时间(开始记录时间)
		if(null!=custServiceEventSearchVO.getRecorderEndTime()){
			strBuf.append(" and cse.recorderTime <= :recorderEndTime");
			paramMap.put("recorderEndTime", custServiceEventSearchVO.getRecorderEndTime());
		}


		return strBuf.toString();
	}


	/***
	 * 功能描述:  构造排序条件<br>
	 * @param custServiceEventSearchVO
	 * @return
	 * Create author:高嵩 <br>
	 * Create on:2008-12-23  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	private String ConstructionSort(CustServiceEventSearchVO custServiceEventSearchVO){
		//StringBuffer strBuf = new StringBuffer();
		if(null==custServiceEventSearchVO.getOrderValue() || "".equals(custServiceEventSearchVO.getOrderValue())){
			return " ORDER BY cse.sysInfo.sysDate DESC ";
		}
		else if(custServiceEventSearchVO.getOrderValue().equalsIgnoreCase("eventNo")
				&&  custServiceEventSearchVO.getOrderKey().equalsIgnoreCase("asc")){
			return " ORDER BY cse.eventNo ASC ";
		}else if(custServiceEventSearchVO.getOrderValue().equalsIgnoreCase("eventNo")
				&&  custServiceEventSearchVO.getOrderKey().equalsIgnoreCase("desc")){
			return " ORDER BY cse.eventNo DESC ";
		}
		return null;


	}

	/***
	 * 功能描述: 分页查询关联事件 <br>
	 * @param custServiceEventSearchVO
	 * @return Page
	 * @roseuid 4943693E032C
	 * Create author:高嵩 <br>
	 * Create on:2008-12-23  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public Page queryAssociatedCSEventByPage(CustServiceEventSearchVO custServiceEventSearchVO) {
		String sql =sqlConfig.getSql(QUERY_ASSOCIATECS_EVENT);
		Page page = super.pagedSqlQueryByPageNo(sql, custServiceEventSearchVO.getPageNo(), custServiceEventSearchVO.getPageSize(), new Object[]{custServiceEventSearchVO.getKeyword()});
		List<Object[]> list = page.getResult();
		page.setResult(assembleCustServiceEventD2V(list));
		return page;


	}
	/***
	 * 功能描述:  <br>
	 * @param result
	 * @return
	 * Create author:高嵩 <br>
	 * Create on:2008-12-24  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	private List<CustServiceEventVO> assembleCustServiceEventD2V(List<Object[]> result) {
		List<CustServiceEventVO> temp=new ArrayList<CustServiceEventVO>();
		for(Object[] item : result){
			CustServiceEventVO custServiceEventVO=new CustServiceEventVO();
			custServiceEventVO.setKeyword(((BigInteger)item[0]).longValue());
			custServiceEventVO.setEventNo((String)item[1]);//事件编号
			custServiceEventVO.setFullAddress((String)item[2]);//详细地点
			custServiceEventVO.setHouseNumber((String)item[3]);//房号
			custServiceEventVO.setEmergencyDegreeName((String)item[4]);//紧急程度
			custServiceEventVO.setCustServEventNatureName((String)item[5]);//事件性质
			custServiceEventVO.setContact((String)item[6]);//联系人
			custServiceEventVO.setEventContent((String)item[7]);//事件内容
			custServiceEventVO.setValidityStr((String)item[8]);//有效性
			custServiceEventVO.setEventStatuzStr((String)item[9]);//事件状态
			temp.add(custServiceEventVO);
		}
		return temp;
	}


	/***
	 * 功能描述:  获取数据库事件编号最大值<br>
	 * @return
	 * Create author:yandawei <br>
	 * Create on:2008-12-17  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public String getMaxCustomerServiceEventCodeFromDB() {
		Query query = super.createSQLQuery("select EVENT_NO from CE_CUST_SERVICE_EVENT a where a.SYSDATE=(select max(`SYSDATE`) from CE_CUST_SERVICE_EVENT where DATE_FORMAT(`SYSDATE`,'%Y-%m-%d')=CURRENT_DATE )", null);
		Object obj = query.uniqueResult();
		if(BlankUtil.isBlank(obj))
			return null;
		String count = (String) obj;
		return count;
	}

}
